﻿using System;
using System.Linq;
using System.Web;
using System.IO;
using NPOI;
using NPOI.HPSF;
using NPOI.HSSF;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.SS.Util;
using NPOI.POIFS;
using NPOI.Util;
using NPOI.HSSF.Util;
using NPOI.XSSF.UserModel;
using System.Data;
using System.Collections.Generic;
using System.Text;

using System.Data.OleDb;
using System.Data.SqlClient;

namespace Common
{
    /*
    导出Excel包含的功能： 
    1.多表头导出最多支持到三行，表头格式说明 
    相邻父列头之间用’#’分隔,父列头与子列头用空格(’ ‘)分隔,相邻子列头用逗号分隔(‘,’) 
    两行：序号#分公司#组别#本日成功签约单数 预警,续约,流失,合计#累计成功签约单数 预警,续约,流失,合计#任务数#完成比例#排名 
    三行：等级#级别#上期结存 件数,重量,比例#本期调入 收购调入 件数,重量,比例#本期发出 车间投料 件数,重量,比例#本期发出 产品外销百分比 件数,重量,比例#平均值 
    三行时请注意：列头要重复 
    2.添加表头标题功能 
    3.添加序号功能 
    4.根据数据设置列宽

    缺陷： 
    数据内容不能合并列合并行

    改进思路： 
    添加一属性：设置要合并的列，为了实现多列合并可以这样设置{“列1,列2”,”列4”}
        */
    /// <summary>
    /// 利用NPOI实现导出Excel
    /// </summary>
    public class ATNPOIHelper
    {

        #region 初始化

        /// <summary>
        /// 声明 HSSFWorkbook 对象
        /// </summary>
        private static HSSFWorkbook _workbook;

        /// <summary>
        /// 声明 HSSFSheet 对象
        /// </summary>
        private static HSSFSheet _sheet;

        #endregion

        #region Excel导出

        /// <summary>
        /// Excel导出
        /// </summary>
        /// <param name="fileName">文件名称 如果为空或NULL，则默认“新建Excel.xls”</param>
        /// <param name="list"></param>
        /// <param name="ColMergeNum">合计：末行合计时，合并的列数</param>
        /// <param name="method">导出方式 1：WEB导出（默认）2：按文件路径导出</param>
        /// <param name="filePath">文件路径 如果WEB导出，则可以为空；如果按文件路径导出，则默认桌面路径</param>
        public static void Export(string fileName, IList<NPOIModel> list, int ColMergeNum, int method , string filePath)
        {
            // 文件名称
            if (!string.IsNullOrEmpty(fileName))
            {
                if (fileName.IndexOf('.') == -1)
                {
                    fileName += ".xls";
                }
                else
                {
                    fileName = fileName.Substring(1, fileName.IndexOf('.')) + ".xls";
                }
            }
            else
            {
                fileName = "新建Excel.xls";
            }
            // 文件路径
            if (2 == method && string.IsNullOrEmpty(filePath))
            {
                filePath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
            }
            // 调用导出处理程序
            Export(list, ColMergeNum);
            // WEB导出
            if (1 == method)
            {
                System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
                //设置下载的Excel文件名\
                if (System.Web.HttpContext.Current.Request.ServerVariables["http_user_agent"].ToString().IndexOf("Firefox") != -1)
                {
                    //火狐浏览器      
                    System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", "=?UTF-8?B?" + Convert.ToBase64String(System.Text.Encoding.UTF8.GetBytes(fileName)) + "?="));
                }
                else
                {
                    //IE等浏览器
                    System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8)));
                }
                using (MemoryStream ms = new MemoryStream())
                {
                    //将工作簿的内容放到内存流中
                    _workbook.Write(ms);
                    //将内存流转换成字节数组发送到客户端
                    System.Web.HttpContext.Current.Response.BinaryWrite(ms.GetBuffer());
                    HttpContext.Current.ApplicationInstance.CompleteRequest();
                    //System.Web.HttpContext.Current.Response.End();
                    _sheet = null;
                    _workbook = null;
                }
            }
            else if (2 == method)
            {
                using (FileStream fs = File.Open(filePath, FileMode.Append))
                {
                    _workbook.Write(fs);
                    _sheet = null;
                    _workbook = null;
                }
            }
        }

        /// <summary>
        /// 导出方法实现
        /// </summary>
        /// <param name="list"></param>
        private static void Export(IList<NPOIModel> list, int ColMergeNum)
        {

            #region 变量声明

            // 初始化
            _workbook = new HSSFWorkbook();
            // 声明 Row 对象
            IRow _row;
            // 声明 Cell 对象
            ICell _cell;
            // 总列数
            int cols = 0;
            // 总行数
            int rows = 0;
            // 行数计数器
            int rowIndex = 0;
            // 单元格值
            string drValue = null;

            #endregion

            foreach (NPOIModel model in list)
            {
                // 工作薄命名
                if (model.sheetName != null)
                    _sheet = (HSSFSheet)_workbook.CreateSheet(model.sheetName);
                else
                    _sheet = (HSSFSheet)_workbook.CreateSheet();

                // 获取数据源
                DataTable dt = model.dataSource;
                // 初始化
                rowIndex = 0;
                // 获取总行数
                rows = GetRowCount(model.headerName);
                // 获取总列数
                cols = GetColCount(model.headerName);

                //合计：合并表格末行N列,rows为表头行数，dt.Rows.Count为数据行数
                if (ColMergeNum > 1)
                {
                    CellRangeAddress region_Merge = new CellRangeAddress(rows + dt.Rows.Count, rows + dt.Rows.Count, 0, ColMergeNum - 1);
                    _sheet.AddMergedRegion(region_Merge);
                }

                ICellStyle myBodyStyle = bodyStyle;
                ICellStyle myTitleStyle = titleStyle;
                ICellStyle myDateStyle = dateStyle;
                ICellStyle myBodyRightStyle = bodyRightStyle;
                // 循环行数
                foreach (DataRow row in dt.Rows)
                {

                    #region 新建表，填充表头，填充列头，样式

                    if (rowIndex == 65535 || rowIndex == 0)
                    {
                        if (rowIndex != 0)
                            _sheet = (HSSFSheet)_workbook.CreateSheet();

                        // 构建行
                        for (int i = 0; i < rows + model.isTitle; i++)
                        {
                            _row = _sheet.GetRow(i);
                            // 创建行
                            if (_row == null)
                                _row = _sheet.CreateRow(i);

                            for (int j = 0; j < cols; j++)
                                _row.CreateCell(j).CellStyle = myBodyStyle;
                        }

                        // 如果存在表标题
                        if (model.isTitle > 0)
                        {
                            // 获取行
                            _row = _sheet.GetRow(0);
                            // 合并单元格
                            CellRangeAddress region = new CellRangeAddress(0, 0, 0, (cols - 1));
                            _sheet.AddMergedRegion(region);
                            // 填充值
                            _row.CreateCell(0).SetCellValue(model.tableTitle);
                            // 设置样式
                            _row.GetCell(0).CellStyle = myTitleStyle;
                            // 设置行高
                            _row.HeightInPoints = 20;
                        }

                        // 取得上一个实体
                        NPOIHeader lastRow = null;
                        IList<NPOIHeader> hList = GetHeaders(model.headerName, rows, model.isTitle);
                        // 创建表头
                        foreach (NPOIHeader m in hList)
                        {
                            var data = hList.Where(c => c.firstRow == m.firstRow && c.lastCol == m.firstCol - 1);
                            if (data.Count() > 0)
                            {
                                lastRow = data.First();
                                if (m.headerName == lastRow.headerName)
                                    m.firstCol = lastRow.firstCol;
                            }

                            // 获取行
                            _row = _sheet.GetRow(m.firstRow);
                            // 合并单元格
                            CellRangeAddress region = new CellRangeAddress(m.firstRow, m.lastRow, m.firstCol, m.lastCol);

                            _sheet.AddMergedRegion(region);
                            // 填充值
                            _row.CreateCell(m.firstCol).SetCellValue(m.headerName);
                        }
                        // 填充表头样式
                        for (int i = 0; i < rows + model.isTitle; i++)
                        {
                            _row = _sheet.GetRow(i);
                            for (int j = 0; j < cols; j++)
                            {
                                _row.GetCell(j).CellStyle = myBodyStyle;
                                //设置列宽
                                _sheet.SetColumnWidth(j, (model.colWidths[j] < 50 ? model.colWidths[j] : 50 + 1) * 450);

                            }
                        }

                        rowIndex = (rows + model.isTitle);
                    }

                    #endregion

                    #region 填充内容
                    //if (rowIndex > 2)
                    //{
                    //    CellRangeAddress region = new CellRangeAddress(rowIndex, rowIndex, 1, 5);
                    //    _sheet.AddMergedRegion(region);
                    
                    //}
                    //if (rowIndex == dt.Rows.Count-1)
                    //{
                    //    CellRangeAddress region = new CellRangeAddress(rowIndex, rowIndex, 1, 7);
                    //    _sheet.AddMergedRegion(region);
                    //}
                    // 构建列
                    _row = _sheet.CreateRow(rowIndex);
                    foreach (DataColumn column in dt.Columns)
                    {
                        // 添加序号列
                        if (1 == model.isOrderby && column.Ordinal == 0)
                        {
                            _cell = _row.CreateCell(0);
                            _cell.SetCellValue(rowIndex - rows);
                            _cell.CellStyle = myBodyStyle;
                        }

                        // 创建列
                        _cell = _row.CreateCell(column.Ordinal + model.isOrderby);

                        // 获取值
                        drValue = row[column].ToString();

                        switch (column.DataType.ToString())
                        {
                            case "System.String"://字符串类型
                                _cell.SetCellValue(drValue);
                                _cell.CellStyle = myBodyStyle;
                                break;
                            case "System.DateTime"://日期类型
                                DateTime dateV;
                                DateTime.TryParse(drValue, out dateV);
                                if(drValue!="")
                                _cell.SetCellValue(dateV);
                                else
                                    _cell.SetCellValue("");

                                _cell.CellStyle = myDateStyle;//格式化显示
                                break;
                            case "System.Boolean"://布尔型
                                bool boolV = false;
                                bool.TryParse(drValue, out boolV);
                                _cell.SetCellValue(boolV);
                                _cell.CellStyle = myBodyStyle;
                                break;
                            case "System.Int16"://整型
                            case "System.Int32":
                            case "System.Int64":
                            case "System.Byte":
                                int intV = 0;
                                int.TryParse(drValue, out intV);
                                _cell.SetCellValue(intV);
                                _cell.CellStyle = myBodyRightStyle;
                                break;
                            case "System.Decimal"://浮点型
                            case "System.Double":
                                double doubV = 0;
                                double.TryParse(drValue, out doubV);
                                _cell.SetCellValue(doubV.ToString("f2"));
                                _cell.CellStyle = myBodyRightStyle;
                                break;
                            case "System.DBNull"://空值处理
                                _cell.SetCellValue("");
                                break;
                            default:
                                _cell.SetCellValue("");
                                break;
                        }

                    }

                    #endregion

                    rowIndex++;
                }
            }
        }

        #region 辅助方法

        /// <summary>
        /// 表头解析
        /// </summary>
        /// <remarks>
        /// </remarks>
        /// <param name="header">表头</param>
        /// <param name="rows">总行数</param>
        /// <param name="addRows">外加行</param>
        /// <param name="addCols">外加列</param>
        /// <returns></returns>
        private static IList<NPOIHeader> GetHeaders(string header, int rows, int addRows)
        {
            // 临时表头数组
            string[] tempHeader;
            string[] tempHeader2;
            // 所跨列数
            int colSpan = 0;
            // 所跨行数
            int rowSpan = 0;
            // 单元格对象
            NPOIHeader model = null;
            // 行数计数器
            int rowIndex = 0;
            // 列数计数器
            int colIndex = 0;
            // 
            IList<NPOIHeader> list = new List<NPOIHeader>();
            // 初步解析
            string[] headers = header.Split(new string[] { "#" }, StringSplitOptions.RemoveEmptyEntries);
            // 表头遍历
            for (int i = 0; i < headers.Length; i++)
            {
                // 行数计数器清零
                rowIndex = 0;
                // 列数计数器清零
                colIndex = 0;
                // 获取所跨行数
                rowSpan = GetRowSpan(headers[i], rows);
                // 获取所跨列数
                colSpan = GetColSpan(headers[i]);

                // 如果所跨行数与总行数相等，则不考虑是否合并单元格问题
                if (rows == rowSpan)
                {
                    colIndex = GetMaxCol(list);
                    model = new NPOIHeader(headers[i],
                        addRows,
                        (rowSpan - 1 + addRows),
                        colIndex,
                        (colSpan - 1 + colIndex),
                        addRows);
                    list.Add(model);
                    rowIndex += (rowSpan - 1) + addRows;
                }
                else
                {
                    // 列索引
                    colIndex = GetMaxCol(list);
                    // 如果所跨行数不相等，则考虑是否包含多行
                    tempHeader = headers[i].Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries);
                    for (int j = 0; j < tempHeader.Length; j++)
                    {

                        // 如果总行数=数组长度
                        if (1 == GetColSpan(tempHeader[j]))
                        {
                            if (j == tempHeader.Length - 1 && tempHeader.Length < rows)
                            {
                                model = new NPOIHeader(tempHeader[j],
                                    (j + addRows),
                                    (j + addRows) + (rows - tempHeader.Length),
                                    colIndex,
                                    (colIndex + colSpan - 1),
                                    addRows);
                                list.Add(model);
                            }
                            else
                            {
                                model = new NPOIHeader(tempHeader[j],
                                        (j + addRows),
                                        (j + addRows),
                                        colIndex,
                                        (colIndex + colSpan - 1),
                                        addRows);
                                list.Add(model);
                            }
                        }
                        else
                        {
                            // 如果所跨列数不相等，则考虑是否包含多列
                            tempHeader2 = tempHeader[j].Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries);
                            for (int m = 0; m < tempHeader2.Length; m++)
                            {
                                // 列索引
                                colIndex = GetMaxCol(list) - colSpan + m;
                                if (j == tempHeader.Length - 1 && tempHeader.Length < rows)
                                {
                                    model = new NPOIHeader(tempHeader2[m],
                                        (j + addRows),
                                        (j + addRows) + (rows - tempHeader.Length),
                                        colIndex,
                                        colIndex,
                                        addRows);
                                    list.Add(model);
                                }
                                else
                                {
                                    model = new NPOIHeader(tempHeader2[m],
                                            (j + addRows),
                                            (j + addRows),
                                            colIndex,
                                            colIndex,
                                            addRows);
                                    list.Add(model);
                                }
                            }
                        }
                        rowIndex += j + addRows;
                    }
                }
            }
            return list;
        }

        /// <summary>
        /// 获取最大列
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        private static int GetMaxCol(IList<NPOIHeader> list)
        {
            int maxCol = 0;
            if (list.Count > 0)
            {
                foreach (NPOIHeader model in list)
                {
                    if (maxCol < model.lastCol)
                        maxCol = model.lastCol;
                }
                maxCol += 1;
            }

            return maxCol;
        }

        /// <summary>
        /// 获取表头行数
        /// </summary>
        /// <param name="newHeaders">表头文字</param>
        /// <returns></returns>
        private static int GetRowCount(string newHeaders)
        {
            string[] ColumnNames = newHeaders.Split(new char[] { '@' });
            int Count = 0;
            if (ColumnNames.Length <= 1)
                ColumnNames = newHeaders.Split(new char[] { '#' });
            foreach (string name in ColumnNames)
            {
                int TempCount = name.Split(new char[] { ' ' }).Length;
                if (TempCount > Count)
                    Count = TempCount;
            }
            return Count;
        }

        /// <summary>
        /// 获取表头列数
        /// </summary>
        /// <param name="newHeaders">表头文字</param>
        /// <returns></returns>
        private static int GetColCount(string newHeaders)
        {
            string[] ColumnNames = newHeaders.Split(new char[] { '@' });
            int Count = 0;
            if (ColumnNames.Length <= 1)
                ColumnNames = newHeaders.Split(new char[] { '#' });
            Count = ColumnNames.Length;
            foreach (string name in ColumnNames)
            {
                int TempCount = name.Split(new char[] { ',' }).Length;
                if (TempCount > 1)
                    Count += TempCount - 1;
            }
            return Count;
        }

        /// <summary>
        /// 列头跨列数
        /// </summary>
        /// <remarks>
        /// </remarks>
        /// <param name="newHeaders">表头文字</param>
        /// <returns></returns>
        private static int GetColSpan(string newHeaders)
        {
            return newHeaders.Split(',').Count();
        }

        /// <summary>
        /// 列头跨行数
        /// </summary> 
        /// <remarks>
        /// </remarks>
        /// <param name="newHeaders">列头文本</param>
        /// <param name="rows">表头总行数</param>
        /// <returns></returns>
        private static int GetRowSpan(string newHeaders, int rows)
        {
            int Count = newHeaders.Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries).Length;
            // 如果总行数与当前表头所拥有行数相等
            if (rows == Count)
                Count = 1;
            else if (Count < rows)
                Count = 1 + (rows - Count);
            else
                throw new Exception("表头格式不正确！");
            return Count;
        }

        #endregion

        #region 单元格样式

        /// <summary>
        /// 数据单元格样式
        /// </summary>
        private static ICellStyle bodyStyle
        {
            get
            {
                ICellStyle style = _workbook.CreateCellStyle();
                style.Alignment = HorizontalAlignment.CENTER; //居中
                style.VerticalAlignment = VerticalAlignment.CENTER;//垂直居中 
                style.WrapText = true;//自动换行
                // 边框
                style.BorderBottom = BorderStyle.THIN;
                style.BorderLeft = BorderStyle.THIN;
                style.BorderRight = BorderStyle.THIN;
                style.BorderTop = BorderStyle.THIN;
                // 字体
                //IFont font = _workbook.CreateFont();
                //font.FontHeightInPoints = 10;
                //font.FontName = "宋体";
                //style.SetFont(font);

                return style;
            }
        }

        /// <summary>
        /// 数据单元格样式
        /// </summary>
        private static ICellStyle bodyRightStyle
        {
            get
            {
                ICellStyle style = _workbook.CreateCellStyle();
                style.Alignment = HorizontalAlignment.RIGHT; //居中
                style.VerticalAlignment = VerticalAlignment.CENTER;//垂直居中 
                style.WrapText = true;//自动换行
                // 边框
                style.BorderBottom = BorderStyle.THIN;
                style.BorderLeft = BorderStyle.THIN;
                style.BorderRight = BorderStyle.THIN;
                style.BorderTop = BorderStyle.THIN;
                // 字体
                //IFont font = _workbook.CreateFont();
                //font.FontHeightInPoints = 10;
                //font.FontName = "宋体";
                //style.SetFont(font);

                return style;
            }
        }

        /// <summary>
        /// 标题单元格样式
        /// </summary>
        private static ICellStyle titleStyle
        {
            get
            {
                ICellStyle style = _workbook.CreateCellStyle();
                style.Alignment = HorizontalAlignment.CENTER; //居中
                style.VerticalAlignment = VerticalAlignment.CENTER;//垂直居中 
                style.WrapText = true;//自动换行 

                //IFont font = _workbook.CreateFont();
                //font.FontHeightInPoints = 14;
                //font.FontName = "宋体";
                //font.Boldweight = (short)FontBoldWeight.BOLD;
                //style.SetFont(font);

                return style;
            }
        }

        /// <summary>
        /// 日期单元格样式
        /// </summary>
        private static ICellStyle dateStyle
        {
            get
            {
                ICellStyle style = _workbook.CreateCellStyle();
                style.Alignment = HorizontalAlignment.CENTER; //居中
                style.VerticalAlignment = VerticalAlignment.CENTER;//垂直居中 
                style.WrapText = true;//自动换行
                // 边框
                style.BorderBottom = BorderStyle.THIN;
                style.BorderLeft = BorderStyle.THIN;
                style.BorderRight = BorderStyle.THIN;
                style.BorderTop = BorderStyle.THIN;
                // 字体
                //IFont font = _workbook.CreateFont();
                //font.FontHeightInPoints = 10;
                //font.FontName = "宋体";
                //style.SetFont(font);

                IDataFormat format = _workbook.CreateDataFormat();
                style.DataFormat = format.GetFormat("yyyy-MM-dd");
                return style;
            }
        }

        #endregion

        #endregion

        #region 根据模板导出 --需要根据自己的Excel模板
        public static string ExportForModul(string sFilePath,string sFileSavePath, DataTable dtEmployee,DataTable dtEmployeXLJY,DataTable dtEmployeeGWJL,DataTable dtEmployeeJNDJ,DataTable dtEmployeeHJJL,DataTable dtExamInfo)
        {   
            // 声明 Row 对象
            IRow _row;
            // 声明 Cell 对象
            ICell _cell;
            // 总列数
            int cols = 0;
            // 总行数
            int rows = 0;
            // 行数计数器
            int rowIndex = 0;
            // 单元格值
            string drValue = null;

            // 初始化
            try
            {
                FileStream fs = File.OpenRead(sFilePath);
                _workbook = new HSSFWorkbook(fs);
                _sheet = (HSSFSheet)_workbook.GetSheetAt(0);
                fs.Close();
            }
            catch (Exception e)
            {
                return "1#" + e.Message;
            }


            rowIndex = 2;

            #region 导出员工基本信息
            if(dtEmployee!=null)
                if (dtEmployee.Rows.Count > 0)
                {
                    _row = _sheet.GetRow(rowIndex);
                    _row.GetCell(2).SetCellValue(dtEmployee.Rows[0]["EmployeeName"].ToString());
                    _row.GetCell(4).SetCellValue(dtEmployee.Rows[0]["Sex"].ToString());
                    _row.GetCell(6).SetCellValue(dtEmployee.Rows[0]["Birthday"].ToString());

                    rowIndex++;
                    _row = _sheet.GetRow(rowIndex);
                    _row.GetCell(2).SetCellValue(dtEmployee.Rows[0]["Education"].ToString());
                    _row.GetCell(4).SetCellValue(dtEmployee.Rows[0]["Nation"].ToString());
                    _row.GetCell(6).SetCellValue(dtEmployee.Rows[0]["LicenceDate2"].ToString());

                    rowIndex++;
                    _row = _sheet.GetRow(rowIndex);
                    _row.GetCell(2).SetCellValue(dtEmployee.Rows[0]["Job"].ToString());
                    _row.GetCell(4).SetCellValue(dtEmployee.Rows[0]["TechnicalTitle"].ToString());
                    _row.GetCell(6).SetCellValue(dtEmployee.Rows[0]["EntryTime"].ToString());

                    rowIndex++;
                    _row = _sheet.GetRow(rowIndex);
                    _row.GetCell(2).SetCellValue(dtEmployee.Rows[0]["RYLY"].ToString());
                    _row.GetCell(4).SetCellValue(dtEmployee.Rows[0]["WorkShop"].ToString());
                    _row.GetCell(6).SetCellValue(dtEmployee.Rows[0]["ClassGroup"].ToString());
                    _row.GetCell(8).SetCellValue(dtEmployee.Rows[0]["IsClassLeader"].ToString());

                    if(dtEmployee.Rows[0]["Photograph"]!=null)
                        if (dtEmployee.Rows[0]["Photograph"].ToString() != "")
                        {
                            byte[] picBytes = (byte[])dtEmployee.Rows[0]["Photograph"];

                            //插入图片
                            if (picBytes != null && picBytes.Length > 0)
                            { 
                                int pictureIdx = _workbook.AddPicture(picBytes, NPOI.SS.UserModel.PictureType.PNG);  //添加图片

                                HSSFPatriarch patriarch = (HSSFPatriarch)_sheet.CreateDrawingPatriarch();

                                HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 240, 7, 2, 9, rowIndex-1);

                                //图片位置，图片左上角为（col, row）
                                HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
                               // pict.Resize(); //用图片原始大小来显示
                                picBytes = null;
                            }
                        }
                }
            #endregion

            rowIndex =8;
            #region 导出职工学历教育信息
            if(dtEmployeXLJY!=null)
                if (dtEmployeXLJY.Rows.Count > 0)
                {
                    int istart = dtEmployeXLJY.Rows.Count > 3 ? dtEmployeXLJY.Rows.Count - 3 : 0;
                    for (int i = istart; i < dtEmployeXLJY.Rows.Count; i++)
                    {

                        _row = _sheet.GetRow(rowIndex);
                        _row.GetCell(1).SetCellValue(dtEmployeXLJY.Rows[i]["JBXX"].ToString());
                        _row.GetCell(3).SetCellValue(dtEmployeXLJY.Rows[i]["ZYMC"].ToString());
                        _row.GetCell(4).SetCellValue(dtEmployeXLJY.Rows[i]["XLCC"].ToString());
                        _row.GetCell(5).SetCellValue(dtEmployeXLJY.Rows[i]["XXFS"].ToString());
                        _row.GetCell(6).SetCellValue(dtEmployeXLJY.Rows[i]["RXSJ"].ToString());
                        _row.GetCell(7).SetCellValue(dtEmployeXLJY.Rows[i]["BYSJ"].ToString());
                        rowIndex++;
                    }
                }
            #endregion
            rowIndex = 12;
            #region 导出岗位经历信息
            if (dtEmployeeGWJL != null)
                if (dtEmployeeGWJL.Rows.Count > 0)
                {
                    int istart = dtEmployeeGWJL.Rows.Count > 4 ? dtEmployeeGWJL.Rows.Count - 4 : 0;
                    for (int i = istart; i < dtEmployeeGWJL.Rows.Count; i++)
                    {

                        _row = _sheet.GetRow(rowIndex);
                        _row.GetCell(1).SetCellValue(dtEmployeeGWJL.Rows[i]["BDSJ"].ToString());
                        _row.GetCell(3).SetCellValue(dtEmployeeGWJL.Rows[i]["BDQZM"].ToString());
                        _row.GetCell(5).SetCellValue(dtEmployeeGWJL.Rows[i]["BDHZM"].ToString());
                        _row.GetCell(7).SetCellValue(dtEmployeeGWJL.Rows[i]["BZ"].ToString()); 
                        rowIndex++;
                    }
                }
            #endregion
            rowIndex = 17;
            #region 导出技能等级信息
            if (dtEmployeeJNDJ != null)
                if (dtEmployeeJNDJ.Rows.Count > 0)
                {
                    int istart = dtEmployeeJNDJ.Rows.Count > 1 ? dtEmployeeJNDJ.Rows.Count - 1 : 0;
                    for (int i = istart; i < dtEmployeeJNDJ.Rows.Count; i++)
                    {
                        _row = _sheet.GetRow(rowIndex);
                        _row.GetCell(2).SetCellValue(dtEmployeeJNDJ.Rows[i]["CJ"].ToString());
                        _row.GetCell(3).SetCellValue(dtEmployeeJNDJ.Rows[i]["ZJ"].ToString());
                        _row.GetCell(4).SetCellValue(dtEmployeeJNDJ.Rows[i]["GJ"].ToString());
                        _row.GetCell(5).SetCellValue(dtEmployeeJNDJ.Rows[i]["JS"].ToString());
                        _row.GetCell(6).SetCellValue(dtEmployeeJNDJ.Rows[i]["GJJS"].ToString());
                        _row.GetCell(7).SetCellValue(dtEmployeeJNDJ.Rows[i]["BZ"].ToString());
                        //rowIndex++;
                    }
                }
            #endregion
            rowIndex = 20;
            #region 导出技能竞赛获奖记录信息
            if (dtEmployeeHJJL != null)
                if (dtEmployeeHJJL.Rows.Count > 0)
                {
                    int istart = dtEmployeeHJJL.Rows.Count > 7 ? dtEmployeeHJJL.Rows.Count - 7 : 0;
                    for (int i = istart; i < dtEmployeeHJJL.Rows.Count; i++)
                    {
                        _row = _sheet.GetRow(rowIndex);
                        _row.GetCell(0).SetCellValue(dtEmployeeHJJL.Rows[i]["SJ"].ToString());
                        _row.GetCell(2).SetCellValue(dtEmployeeHJJL.Rows[i]["JBDW"].ToString());
                        _row.GetCell(3).SetCellValue(dtEmployeeHJJL.Rows[i]["JSLB"].ToString());
                        _row.GetCell(4).SetCellValue(dtEmployeeHJJL.Rows[i]["JSMC"].ToString());
                        _row.GetCell(6).SetCellValue(dtEmployeeHJJL.Rows[i]["QDMC"].ToString());
                        _row.GetCell(7).SetCellValue(dtEmployeeHJJL.Rows[i]["HJCH"].ToString());
                        rowIndex++;
                    }
                }
            #endregion


            #region 导出培训考核记录
            rowIndex = 3;
            if (dtExamInfo != null)
                if (dtExamInfo.Rows.Count > 0)
                {
                    int istart = dtExamInfo.Rows.Count > 24 ? dtExamInfo.Rows.Count - 24 : 0;
                    for (int i = istart; i < dtExamInfo.Rows.Count; i++)
                    {
                        _row = _sheet.GetRow(rowIndex);
                        _row.GetCell(10).SetCellValue(dtExamInfo.Rows[i]["paperName"].ToString());
                        _row.GetCell(16).SetCellValue(dtExamInfo.Rows[i]["pxkssj"].ToString());
                        _row.GetCell(18).SetCellValue(dtExamInfo.Rows[i]["pxjssj"].ToString());
                        _row.GetCell(20).SetCellValue(dtExamInfo.Rows[i]["ksjg"].ToString()); 
                        rowIndex++;
                    }
                }
            #endregion



            //工作表和sheet置空

            using (FileStream fs = File.OpenWrite(sFileSavePath))
            {
                _workbook.Write(fs);
                _sheet = null;
                _workbook = null;
            }
            return "0#导出成功！";
        }
        #endregion
    }

    /// <summary>
    /// 实体类
    /// </summary>
    public class NPOIModel
    {
        /// <summary>
        /// 数据源
        /// </summary>
        public DataTable dataSource { get; private set; }
        /// <summary>
        /// 要导出的数据列数组
        /// </summary>
        public string[] fileds { get; private set; }
        /// <summary>
        /// 工作薄名称数组
        /// </summary>
        public string sheetName { get; private set; }
        /// <summary>
        /// 表标题
        /// </summary>
        public string tableTitle { get; private set; }
        /// <summary>
        /// 表标题是否存在 1：存在 0：不存在
        /// </summary>
        public int isTitle { get; private set; }
        /// <summary>
        /// 是否添加序号
        /// </summary>
        public int isOrderby { get; private set; }
        /// <summary>
        /// 表头
        /// </summary>
        public string headerName { get; private set; }
        /// <summary>
        /// 取得列宽
        /// </summary>
        public int[] colWidths { get; private set; }
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <remarks>
        /// </remarks>
        /// <param name="dataSource">数据来源 DataTable</param>
        /// <param name="filed">要导出的字段，如果为空或NULL，则默认全部</param> 
        /// <param name="sheetName">工作薄名称</param>
        /// <param name="headerName">表头名称 如果为空或NULL，则默认数据列字段
        /// 相邻父列头之间用'#'分隔,父列头与子列头用空格(' ')分隔,相邻子列头用逗号分隔(',')
        /// 两行：序号#分公司#组别#本日成功签约单数 预警,续约,流失,合计#累计成功签约单数 预警,续约,流失,合计#任务数#完成比例#排名 
        /// 三行：等级#级别#上期结存 件数,重量,比例#本期调入 收购调入 件数,重量,比例#本期发出 车间投料 件数,重量,比例#本期发出 产品外销百分比 件数,重量,比例#平均值 
        /// 三行时请注意：列头要重复
        /// </param>
        /// <param name="tableTitle">表标题</param> 
        /// <param name="isOrderby">是否添加序号 0：不添加 1：添加</param>
        public NPOIModel(DataTable dataSource, string filed, string sheetName, string headerName, string tableTitle=null, int isOrderby = 0)
        {
            if (!string.IsNullOrEmpty(filed))
            {
                this.fileds = filed.ToUpper().Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries);

                // 移除多余数据列
                for (int i = dataSource.Columns.Count - 1; i >= 0; i--)
                {
                    DataColumn dc = dataSource.Columns[i];
                    if (!this.fileds.Contains(dataSource.Columns[i].Caption.ToUpper()))
                    {
                        dataSource.Columns.Remove(dataSource.Columns[i]);
                    }
                }

                // 列索引
                int colIndex = 0;
                // 循环排序
                for (int i = 0; i < dataSource.Columns.Count; i++)
                {
                    // 获取索引
                    colIndex = GetColIndex(dataSource.Columns[i].Caption.ToUpper());
                    // 设置下标
                    dataSource.Columns[i].SetOrdinal(colIndex);
                }
            }
            else
            {
                this.fileds = new string[dataSource.Columns.Count];
                for (int i = 0; i < dataSource.Columns.Count; i++)
                {
                    this.fileds[i] = dataSource.Columns[i].ColumnName;
                }
            }
            this.dataSource = dataSource;

            if (!string.IsNullOrEmpty(sheetName))
            {
                this.sheetName = sheetName;
            }
            if (!string.IsNullOrEmpty(headerName))
            {
                this.headerName = headerName;
            }
            else
            {
                this.headerName = string.Join("#", this.fileds);
            }
            if (!string.IsNullOrEmpty(tableTitle))
            {
                this.tableTitle = tableTitle;
                this.isTitle = 1;
            }
            // 取得数据列宽 数据列宽可以和表头列宽比较，采取最长宽度  
            colWidths = new int[this.dataSource.Columns.Count];
            foreach (DataColumn item in this.dataSource.Columns)
            {
                colWidths[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
            }
            // 循环比较最大宽度
            for (int i = 0; i < this.dataSource.Rows.Count; i++)
            {
                for (int j = 0; j < this.dataSource.Columns.Count; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(this.dataSource.Rows[i][j].ToString()).Length;
                    if (intTemp > colWidths[j])
                    {
                        colWidths[j] = intTemp;
                    }
                }
            }
            if (isOrderby > 0)
            {
                this.isOrderby = isOrderby;
                this.headerName = "序号#" + this.headerName;
            }
        }

        /// <summary>
        /// 获取列名下标
        /// </summary>
        /// <param name="colName">列名称</param>
        /// <returns></returns>
        private int GetColIndex(string colName)
        {
            for (int i = 0; i < this.fileds.Length; i++)
            {
                if (colName == this.fileds[i])
                    return i;
            }
            return 0;
        }
    }

    /// <summary>
    /// 表头构建类
    /// </summary>
    public class NPOIHeader
    {
        /// <summary>
        /// 表头
        /// </summary>
        public string headerName { get; set; }
        /// <summary>
        /// 起始行
        /// </summary>
        public int firstRow { get; set; }
        /// <summary>
        /// 结束行
        /// </summary>
        public int lastRow { get; set; }
        /// <summary>
        /// 起始列
        /// </summary>
        public int firstCol { get; set; }
        /// <summary>
        /// 结束列
        /// </summary>
        public int lastCol { get; set; }
        /// <summary>
        /// 是否跨行
        /// </summary>
        public int isRowSpan { get; private set; }
        /// <summary>
        /// 是否跨列
        /// </summary>
        public int isColSpan { get; private set; }
        /// <summary>
        /// 外加行
        /// </summary>
        public int rows { get; set; }

        public NPOIHeader() { }
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="headerName">表头</param>
        /// <param name="firstRow">起始行</param>
        /// <param name="lastRow">结束行</param>
        /// <param name="firstCol">起始列</param>
        /// <param name="lastCol">结束列</param>
        /// <param name="rows">外加行</param>
        /// <param name="cols">外加列</param>
        public NPOIHeader(string headerName, int firstRow, int lastRow, int firstCol, int lastCol, int rows = 0)
        {
            this.headerName = headerName;
            this.firstRow = firstRow;
            this.lastRow = lastRow;
            this.firstCol = firstCol;
            this.lastCol = lastCol;
            // 是否跨行判断
            if (firstRow != lastRow)
                isRowSpan = 1;
            if (firstCol != lastCol)
                isColSpan = 1;

            this.rows = rows;
        }
    }


    /// <summary>
    ///NPOIHelper 的摘要说明
    /// </summary>
    public class NPOIHelper
    {
        #region 由DataSet、DataTable导出Excel
        /// <summary>
        /// 由DataSet导出Excel,被外界调用的方法
        /// </summary>   
        /// <param name="sourceTable">要导出数据的DataTable</param>
        /// <param name="fileName">指定Excel工作表名称</param>
        /// <param name="fileName">strType=0:普通格式 1有格式化的形式</param>
        /// <returns>Excel工作表</returns>    
        public static void ExportDataSetToExcel(DataSet sourceDs, string fileName, string sheetName, string strType)
        {
            MemoryStream ms = null;
            if (strType == "0")
            {
                ms = ExportDataSetToBasicExcel(sourceDs, sheetName) as MemoryStream;
            }
            else
            {
                ms = ExportDataSetToFormatExcel(sourceDs, sheetName) as MemoryStream;
            }
            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8) + ".xls");
            HttpContext.Current.Response.BinaryWrite(ms.ToArray());
            HttpContext.Current.Response.End();
            ms.Close();
            ms = null;
        }

        /// <summary>
        /// 由DataSet导出Excel(基本形式)
        /// </summary>
        /// <param name="sourceTable">要导出数据的DataTable</param>    
        /// <param name="sheetName">工作表名称</param>
        /// <returns>Excel工作表</returns>    
        private static Stream ExportDataSetToBasicExcel(DataSet sourceDs, string sheetName)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            MemoryStream ms = new MemoryStream();
            string[] sheetNames = sheetName.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries); //分割符
            for (int i = 0; i < sheetNames.Length; i++)
            {
                HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(sheetNames[i]);

                // handling value.            
                int rowIndex = 0;
                int sheetnum = 1;
                foreach (DataRow row in sourceDs.Tables[i].Rows)
                {
                    #region 创建表头
                    if (rowIndex == 65535 || rowIndex == 0)
                    {
                        if (rowIndex != 0)
                        {
                            sheetnum++;
                            sheet = (HSSFSheet)workbook.CreateSheet(sheetNames[i] + "-" + sheetnum.ToString());
                        }
                        var headerRow = sheet.CreateRow(0);
                        var headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        var font = workbook.CreateFont();
                        font.FontHeightInPoints = 10;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);
                        foreach (DataColumn column in sourceDs.Tables[i].Columns)
                            headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                        rowIndex = 1;
                    }
                    #endregion

                    #region 创建内容
                    HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
                    foreach (DataColumn column in sourceDs.Tables[i].Columns)
                    {
                        dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                    }
                    rowIndex++;
                    #endregion
                }
            }
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            workbook = null;
            return ms;
        }

        /// <summary>
        /// 由DataSet导出Excel(带有格式)
        /// </summary>
        /// <param name="sourceTable">要导出数据的DataTable</param>    
        /// <param name="sheetName">工作表名称</param>
        /// <returns>Excel工作表</returns>
        private static Stream ExportDataSetToFormatExcel(DataSet sourceDs, string sheetName)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            MemoryStream ms = new MemoryStream();
            string[] sheetNames = sheetName.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries); //分割符
            for (int i = 0; i < sheetNames.Length; i++)
            {
                HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(sheetNames[i]);


                var dateStyle = workbook.CreateCellStyle();
                var format = workbook.CreateDataFormat();
                dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

                //取得列宽  
                int[] arrColWidth = new int[sourceDs.Tables[i].Columns.Count];
                foreach (DataColumn item in sourceDs.Tables[i].Columns)
                {
                    arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
                }
                for (int k = 0; k < sourceDs.Tables[i].Rows.Count; k++)
                {
                    for (int j = 0; j < sourceDs.Tables[i].Columns.Count; j++)
                    {
                        int intTemp = Encoding.GetEncoding(936).GetBytes(sourceDs.Tables[i].Rows[k][j].ToString()).Length;
                        if (intTemp > arrColWidth[j])
                        {
                            arrColWidth[j] = intTemp;
                        }
                    }
                }

                int rowIndex = 0;
                int sheetnum = 1;
                foreach (DataRow row in sourceDs.Tables[i].Rows)
                {
                    #region 创建表头
                    if (rowIndex == 65535 || rowIndex == 0)
                    {
                        if (rowIndex != 0)
                        {
                            sheetnum++;
                            sheet = (HSSFSheet)workbook.CreateSheet(sheetNames[i] + "-" + sheetnum.ToString());
                        }
                        var headerRow = sheet.CreateRow(0);
                        var headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        var font = workbook.CreateFont();
                        font.FontHeightInPoints = 10;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);

                        //设置边框
                        headStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
                        headStyle.BottomBorderColor = HSSFColor.BLACK.index;
                        headStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
                        headStyle.LeftBorderColor = HSSFColor.GREEN.index;
                        headStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
                        headStyle.RightBorderColor = HSSFColor.BLUE.index;
                        headStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
                        headStyle.TopBorderColor = HSSFColor.ORANGE.index;
                        //设置背景色
                        headStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LIME.index;
                        headStyle.FillPattern = NPOI.SS.UserModel.FillPatternType.BIG_SPOTS;
                        headStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.LIGHT_GREEN.index;

                        foreach (DataColumn column in sourceDs.Tables[i].Columns)
                        {
                            headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                            headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
                            //设置列宽  
                            sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
                        }
                        rowIndex = 1;
                    }
                    #endregion

                    #region 创建内容
                    var dataRow = sheet.CreateRow(rowIndex);
                    foreach (DataColumn column in sourceDs.Tables[i].Columns)
                    {
                        var newCell = dataRow.CreateCell(column.Ordinal);

                        string drValue = row[column].ToString();

                        switch (column.DataType.ToString())
                        {
                            case "System.String"://字符串类型  
                                newCell.SetCellValue(drValue);
                                break;
                            case "System.DateTime"://日期类型  
                            case "MySql.Data.Types.MySqlDateTime": //MySql类型
                                if (drValue == "0000/0/0 0:00:00" || String.IsNullOrEmpty(drValue))
                                {
                                    //当时间为空，防止生成的execl 中是一串“#######”号，所有赋值为空字符串
                                    newCell.SetCellValue("");
                                }
                                else
                                {
                                    DateTime dateV;
                                    DateTime.TryParse(drValue, out dateV);
                                    newCell.SetCellValue(dateV);
                                    newCell.CellStyle = dateStyle;//格式化显示  
                                }
                                break;
                            case "System.Boolean"://布尔型  
                                bool boolV = false;
                                bool.TryParse(drValue, out boolV);
                                newCell.SetCellValue(boolV);
                                break;
                            case "System.Int16"://整型  
                            case "System.Int32":
                            case "System.Int64":
                            case "System.Byte":
                                int intV = 0;
                                int.TryParse(drValue, out intV);
                                newCell.SetCellValue(intV);
                                break;
                            case "System.Decimal"://浮点型  
                            case "System.Double":
                                double doubV = 0;
                                double.TryParse(drValue, out doubV);
                                newCell.SetCellValue(doubV);
                                break;
                            case "System.DBNull"://空值处理  
                                newCell.SetCellValue("");
                                break;
                            default:
                                newCell.SetCellValue("");
                                break;
                        }
                    }
                    #endregion
                    rowIndex++;
                }
                //设置首行首列冻结
                //第一个参数表示要冻结的列数
                //第二个参数表示要冻结的行数
                //第三个参数表示右边区域可见的首列序号，从1开始计算
                //第四个参数表示下边区域可见的首行序号，也是从1开始计算
                sheet.CreateFreezePane(1, 1, 0, 10);
            }
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            workbook = null;
            return ms;
        }
        /// <summary>
        /// 由DataTable导出Excel,基本方法
        /// </summary>
        /// <param name="sourceTable">要导出数据的DataTable</param> 
        /// <returns>Excel工作表</returns>    
        private static Stream ExportDataTableToBasicExcel(DataTable sourceTable, string sheetName)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            MemoryStream ms = new MemoryStream();
            var sheet = workbook.CreateSheet(sheetName);

            int rowIndex = 0;
            int sheetnum = 1;
            foreach (DataRow row in sourceTable.Rows)
            {
                #region 创建表头
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheetnum++;
                        sheet = workbook.CreateSheet(sheetName + "-" + sheetnum.ToString());
                    }
                    var headerRow = sheet.CreateRow(0);
                    var headStyle = workbook.CreateCellStyle();
                    headStyle.Alignment = HorizontalAlignment.CENTER;
                    var font = workbook.CreateFont();
                    font.FontHeightInPoints = 12;
                    font.Boldweight = 700;
                    headStyle.SetFont(font);
                    foreach (DataColumn column in sourceTable.Columns)
                        headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                    rowIndex = 1;
                }
                #endregion

                HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
                foreach (DataColumn column in sourceTable.Columns)
                {
                    dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                }



                ////修改 BY CJH 2018-5-28
                //// 合并单元格 add BY CJH
                //HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);

                //CellRangeAddress region = new CellRangeAddress(rowIndex, rowIndex, 1, 5);
                //sheet.AddMergedRegion(region);
                //foreach (DataColumn column in sourceTable.Columns)
                //{
                //    dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                //}

                rowIndex++;
            }
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            sheet = null;
            workbook = null;
            return ms;
        }


        /// <summary>
        /// 带格式化的
        /// </summary>
        /// <param name="dtSource">数据源</param>
        /// <param name="sheetName">创建的Sheet名称</param>
        /// <returns></returns>
        private static Stream ExportDataTableToFormatExcel(DataTable dtSource, string sheetName)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            var sheet = workbook.CreateSheet(sheetName);

            var dateStyle = workbook.CreateCellStyle();
            var format = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

            //取得列宽  
            int[] arrColWidth = new int[dtSource.Columns.Count];
            foreach (DataColumn item in dtSource.Columns)
            {
                arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
            }
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                for (int j = 0; j < dtSource.Columns.Count; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
            }

            int rowIndex = 0;

            int sheetnum = 1;

            foreach (DataRow row in dtSource.Rows)
            {
                #region 新建表，填充表头，填充列头，样式
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheetnum++;
                        sheet = workbook.CreateSheet(sheetName + "-" + sheetnum.ToString());
                    }

                    #region 列头及样式
                    {
                        var headerRow = sheet.CreateRow(0);
                        var headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.CENTER;
                        var font = workbook.CreateFont();
                        font.FontHeightInPoints = 10;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);
                        //设置背景色
                        headStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.WHITE.index;
                        headStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.RED.index;


                        foreach (DataColumn column in dtSource.Columns)
                        {
                            headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                            headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
                            //设置列宽  
                            sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
                        }
                    }
                    #endregion
                    rowIndex = 1;
                }
                #endregion


                #region 填充内容
                var dataRow = sheet.CreateRow(rowIndex);
                foreach (DataColumn column in dtSource.Columns)
                {
                    var newCell = dataRow.CreateCell(column.Ordinal);

                    string drValue = row[column].ToString();

                    switch (column.DataType.ToString())
                    {
                        case "System.String"://字符串类型  
                            newCell.SetCellValue(drValue);
                            break;
                        case "System.DateTime"://日期类型  
                        case "MySql.Data.Types.MySqlDateTime": //MySql类型
                            if (drValue == "0000/0/0 0:00:00" || String.IsNullOrEmpty(drValue))
                            {
                                //当时间为空，防止生成的execl 中是一串“#######”号，所有赋值为空字符串
                                newCell.SetCellValue("");
                            }
                            else
                            {
                                DateTime dateV;
                                DateTime.TryParse(drValue, out dateV);
                                newCell.SetCellValue(dateV);

                                newCell.CellStyle = dateStyle;//格式化显示  
                            }
                            break;
                        case "System.Boolean"://布尔型  
                            bool boolV = false;
                            bool.TryParse(drValue, out boolV);
                            newCell.SetCellValue(boolV);
                            break;
                        case "System.Int16"://整型  
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int intV = 0;
                            int.TryParse(drValue, out intV);
                            newCell.SetCellValue(intV);
                            break;
                        case "System.Decimal"://浮点型  
                        case "System.Double":
                            double doubV = 0;
                            double.TryParse(drValue, out doubV);
                            newCell.SetCellValue(doubV);
                            break;
                        case "System.DBNull"://空值处理  
                            newCell.SetCellValue("");
                            break;
                        default:
                            newCell.SetCellValue("");
                            break;
                    }

                }
                #endregion

                rowIndex++;
            }
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                return ms;
            }
        }

        /// <summary>
        /// 由DataTable导出Excel
        /// </summary>
        /// <param name="sourceTable">要导出数据的DataTable</param>
        /// <param name="fileName">指定Excel工作表名称</param>
        /// <param name="sheetName">指定Sheet名称</param>
        /// <param name="strType">strType=0:基本的，1：带有格式的</param>
        /// <returns>Excel工作表</returns>
        public static void ExportDataTableToExcel(DataTable sourceTable, string fileName, string sheetName, string strType)
        {
            MemoryStream ms = null;
            if (strType == "0")
            {
                ms = ExportDataTableToBasicExcel(sourceTable, sheetName) as MemoryStream;
            }
            else
            {
                ms = ExportDataTableToFormatExcel(sourceTable, sheetName) as MemoryStream;
            }

            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.Default) + ".xlsx");
            HttpContext.Current.Response.BinaryWrite(ms.ToArray());
            HttpContext.Current.Response.End();
            ms.Close();
            ms = null;
        }

        /// <summary>
        /// 由DataTable导出Excel(适应于基本的模版导出，且不超过65535条)
        /// </summary>
        /// <param name="sourceTable">要导出数据的DataTable</param>
        /// <param name="modelpath">模版文件实体路径</param>
        /// <param name="modelName">模版文件名称</param>
        /// <param name="fileName">指定Excel工作表名称</param>
        /// <param name="sheetName">作为模型的Excel</param>
        /// <param name="rowindex">从第几行开始写入数据(此为行索引，若为1则从第2行开始写入数据)</param>
        /// <returns>Excel工作表</returns>
        public static void ExportDataTableToExcelModel(DataTable sourceTable, string modelpath, string modelName, string fileName, string sheetName, int rowIndex)
        {
            int colIndex = 0;
            FileStream file = new FileStream(modelpath + "/" + modelName, FileMode.Open, FileAccess.Read);//读入excel模板
            HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
            HSSFSheet sheet1 = (HSSFSheet)hssfworkbook.GetSheet(sheetName);
            if (sourceTable.Rows.Count + rowIndex > 65535)
            {
                throw new ArgumentException("数据太多，系统尚不支持，请缩小查询范围!");
            }

            foreach (DataRow row in sourceTable.Rows)
            {   //双循环写入sourceTable中的数据
                colIndex = 0;
                HSSFRow xlsrow = (HSSFRow)sheet1.CreateRow(rowIndex);
                foreach (DataColumn col in sourceTable.Columns)
                {
                    xlsrow.CreateCell(colIndex).SetCellValue(row[col.ColumnName].ToString());
                    colIndex++;
                }
                rowIndex++;
            }
            sheet1.ForceFormulaRecalculation = true;

            //CS项目适用胡方法
            //FileStream fileS = new FileStream(modelpath + fileName + ".xls", FileMode.Create);//保存
            //hssfworkbook.Write(fileS);
            //fileS.Close();
            MemoryStream ms = new MemoryStream();
            hssfworkbook.Write(ms);

            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8) + ".xls");
            HttpContext.Current.Response.BinaryWrite(ms.ToArray());
            HttpContext.Current.Response.End();
            ms.Close();
            ms = null;
        }
        #endregion

        #region 从Excel中读数据到DataTable
        /// <summary>
        /// 从Excel中获取数据到DataTable
        /// </summary>
        /// <param name="strFileName">Excel文件全路径(服务器路径)</param>
        /// <param name="extension">Excel文件的扩展名</param>
        /// <param name="SheetName">要获取数据的工作表名称</param>
        /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
        /// <returns></returns>
        public static DataTable RenderDataTableFromExcel(string strFileName, string extension, string SheetName, int HeaderRowIndex)
        {
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                IWorkbook workbook = null;
                if (extension.Equals(".xls") || extension.Equals(".XLS"))
                {
                    workbook = new HSSFWorkbook(file);
                }
                else
                {
                    workbook = new XSSFWorkbook(file);
                }
                return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex);
            }
        }

        /// <summary>
        /// 从Excel中获取数据到DataTable
        /// </summary>
        /// <param name="strFileName">Excel文件全路径(服务器路径)</param>
        /// <param name="extension">Excel文件的扩展名</param>
        /// <param name="SheetIndex">要获取数据的工作表序号(从0开始)</param>
        /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
        /// <returns></returns>
        public static DataTable RenderDataTableFromExcel(string strFileName, string extension, int SheetIndex, int HeaderRowIndex)
        {
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                IWorkbook workbook = null;
                if (extension.Equals(".xls") || extension.Equals(".XLS"))
                {
                    workbook = new HSSFWorkbook(file);
                }
                else
                {
                    workbook = new XSSFWorkbook(file);
                }

                string SheetName = workbook.GetSheetName(SheetIndex);
                return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex);
            }
        }

        /// <summary>
        /// 从Excel中获取数据到DataTable
        /// </summary>
        /// <param name="ExcelFileStream">Excel文件流</param>
        /// <param name="SheetName">要获取数据的工作表名称</param>
        /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
        /// <returns></returns>
        public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, string SheetName, int HeaderRowIndex)
        {
            IWorkbook workbook = new HSSFWorkbook(ExcelFileStream);
            ExcelFileStream.Close();
            return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex);
        }

        /// <summary>
        /// 从Excel中获取数据到DataTable
        /// </summary>
        /// <param name="ExcelFileStream">Excel文件流</param>
        /// <param name="SheetIndex">要获取数据的工作表序号(从0开始)</param>
        /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
        /// <returns></returns>
        public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, int SheetIndex, int HeaderRowIndex)
        {
            IWorkbook workbook = new HSSFWorkbook(ExcelFileStream);
            ExcelFileStream.Close();
            string SheetName = workbook.GetSheetName(SheetIndex);
            return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex);
        }

        /// <summary>
        /// 从Excel中获取数据到DataTable
        /// </summary>
        /// <param name="workbook">要处理的工作薄</param>
        /// <param name="SheetName">要获取数据的工作表名称</param>
        /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
        /// <returns></returns>
        public static DataTable RenderDataTableFromExcel(IWorkbook workbook, string SheetName, int HeaderRowIndex)
        {
            ISheet sheet = workbook.GetSheet(SheetName);
            DataTable table = new DataTable();
            try
            {
                IRow headerRow = sheet.GetRow(HeaderRowIndex);
                int cellCount = headerRow.LastCellNum;

                for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                {
                    DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                    table.Columns.Add(column);
                }

                int rowCount = sheet.LastRowNum;

                #region 循环各行各列,写入数据到DataTable
                for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    DataRow dataRow = table.NewRow();
                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        ICell cell = row.GetCell(j);
                        if (cell == null)
                        {
                            dataRow[j] = null;
                        }
                        else
                        {
                            switch (cell.CellType)
                            {
                                case CellType.BLANK:
                                    dataRow[j] = null;
                                    break;
                                case CellType.BOOLEAN:
                                    dataRow[j] = cell.BooleanCellValue;
                                    break;
                                case CellType.NUMERIC:
                                    dataRow[j] = cell.ToString();
                                    break;
                                case CellType.STRING:
                                    dataRow[j] = cell.StringCellValue;
                                    break;
                                case CellType.ERROR:
                                    dataRow[j] = cell.ErrorCellValue;
                                    break;
                                case CellType.FORMULA:
                                default:
                                    dataRow[j] = "=" + cell.CellFormula;
                                    break;
                            }
                        }
                    }
                    table.Rows.Add(dataRow);
                    //dataRow[j] = row.GetCell(j).ToString();
                }
                #endregion
            }
            catch (System.Exception ex)
            {
                table.Clear();
                table.Columns.Clear();
                table.Columns.Add("出错了");
                DataRow dr = table.NewRow();
                dr[0] = ex.Message;
                table.Rows.Add(dr);
                return table;
            }
            finally
            {
                //sheet.Dispose();
                workbook = null;
                sheet = null;
            }
            #region 清除最后的空行
            for (int i = table.Rows.Count - 1; i > 0; i--)
            {
                bool isnull = true;
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    if (table.Rows[i][j] != null)
                    {
                        if (table.Rows[i][j].ToString() != "")
                        {
                            isnull = false;
                            break;
                        }
                    }
                }
                if (isnull)
                {
                    table.Rows[i].Delete();
                }
            }
            #endregion
            return table;
        }

        #endregion






        /// <summary>
        /// 从Excel中读取数据

        /// </summary>
        /// <param name="filePath">excel路径</param>
        /// <returns>DataTable</returns>
        public static bool GetExcel(string filePath, ref DataTable dt)
        {
            string strConn = "Provider=Microsoft.Ace.OleDb.12.0;data source=" + filePath + ";Extended Properties='Excel 12.0; HDR=NO; IMEX=1'";
            using (OleDbConnection conn = new OleDbConnection(strConn))
            {
                try
                {
                    conn.Open();
                    string sql = "select * from [Sheet1$]";
                    OleDbDataAdapter myCommand = new OleDbDataAdapter(sql, strConn);
                    DataSet ds = new DataSet();
                    myCommand.Fill(ds, "table1");
                    conn.Close();
                    dt = ds.Tables[0];
                    return true;
                }
                catch
                {
                    return false;
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 读取Excel
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <param name="dt">DataTable</param>
        /// <param name="fileName">文件名字</param>
        /// <returns>bool</returns>
        public static bool GetExcel(string filePath, ref DataTable dt, string fileName)
        {
            string strConn="";
            if(filePath.Split('.')[1]=="xls")
                strConn = "Provider=Microsoft.Jet.OleDb.4.0;data source=" + filePath + ";Extended Properties='Excel 8.0;HDR=NO; IMEX=1;'";
            if (filePath.Split('.')[1] == "xlsx")
                strConn = "Provider=Microsoft.Ace.OleDb.12.0;data source=" + filePath + ";Extended Properties='Excel 12.0; HDR=NO; IMEX=1'";
            using (OleDbConnection conn = new OleDbConnection(strConn))
            {
                try
                {
                    conn.Open();
                    //string sql = "select * from [" + fileName + "$]";
                    DataTable sheetNames = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

                    string sql = "select * from [" + sheetNames.Rows[0][2].ToString() + "]";

                    OleDbDataAdapter myCommand = new OleDbDataAdapter(sql, strConn);
                    DataSet ds = new DataSet();
                    myCommand.Fill(ds, "table1");
                    conn.Close();
                    dt = ds.Tables[0];
                    return true;
                }
                catch
                {
                    return false;
                }
                finally
                {
                    conn.Close();
                }
            }
        }

        public static bool GetExcel(string filePath, ref DataTable dt, string fileName, out string errorMsg)
        {
            errorMsg = "";
            //  string strConn = "Provider=Microsoft.Ace.OleDb.12.0;data source=" + filePath + ";Extended Properties='Excel 12.0; HDR=NO; IMEX=1'";
            string strConn = "Provider=Microsoft.Jet.OleDb.4.0;data source=" + filePath + ";Extended Properties='Excel 8.0;HDR=NO; IMEX=1;'";
            using (OleDbConnection conn = new OleDbConnection(strConn))
            {
                try
                {
                    conn.Open();
                    string sql = "select * from [" + fileName + "$]";
                    OleDbDataAdapter myCommand = new OleDbDataAdapter(sql, strConn);
                    DataSet ds = new DataSet();
                    myCommand.Fill(ds, "table1");
                    conn.Close();
                    dt = ds.Tables[0];
                    return true;
                }
                catch (Exception ex)
                {
                    errorMsg = ex.Message;
                    return false;
                }
                finally
                {
                    conn.Close();
                }
            }
        }
    }
}
